package com.keti.DaoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.keti.Dao.UserDao;
import com.keti.pojo.User;
import com.keti.pojo.stuff;

import util.DBUtil;

public class UserDaoImpl implements UserDao{
	Connection conn = null;
	PreparedStatement ps =null;
	ResultSet rs = null;

	@Override
	public User Login(String account,String password) {
		conn=DBUtil.getconn();
		User u = null;
		try {
			
			ps=conn.prepareStatement("select * from user where account=? and `password`=?");
			ps.setString(1, account);
			ps.setString(2, password);
			rs=ps.executeQuery();
			while(rs.next()) {
				u=new User();
				u.setId(rs.getInt("id"));
				u.setAccount(rs.getString("account"));
				u.setPassword(rs.getString("password"));
				u.setName(rs.getString("name"));
				u.setSex(rs.getString("sex"));
				u.setAge(rs.getInt("age"));
				u.setIdentity(rs.getString("identity"));
				u.setDepartment(rs.getString("department"));
			
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return u;
	}

	@Override
	public void add(User u) {
		conn=DBUtil.getconn();
		String sql="INSERT `user`(account,PASSWORD,name,sex,age,identity,department) VALUE(?,?,?,?,?,?,?)";
		try {
			ps = conn.prepareStatement(sql);
			String[] params={
					u.getAccount(),
					u.getPassword(),
					u.getName(),
					u.getSex(),
					String.valueOf(u.getAge()),
					u.getIdentity(),
					u.getDepartment(),
					
				};
				DBUtil.exeUpd(sql, params);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				DBUtil.closeAll(conn, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}

	@Override
	public void del(int id) {
		conn = DBUtil.getconn();
		try {
			ps = conn.prepareStatement("DELETE from `user` where id=? and identity!='管理员'");
			ps.setInt(1, id);
			ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	@Override
	public void Updata(User u) {
		conn = DBUtil.getconn();
		String sql="UPDATE `user` set account=?,PASSWORD=?,name=?,sex=?,age=?,department=? where id=?";
		try {
			ps = conn.prepareStatement(sql);
			String[] params= {
					u.getAccount(),
					u.getPassword(),
					u.getName(),
					u.getSex(),
					String.valueOf(u.getAge()),
					u.getDepartment(),
					String.valueOf(u.getId())
					
					
			};
			DBUtil.exeUpd(sql, params);
		
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public List<User> queryAll() {
		List<User> list = new ArrayList<User>();
		conn = DBUtil.getconn();
		try {
			ps = conn.prepareStatement("select *from `user`");
			rs = ps.executeQuery();
			while(rs.next()) {
				int id = rs.getInt(1);
				String account = rs.getString(2);
				String name = rs.getString(4);
				String sex = rs.getString(5);
				int age = rs.getInt(6);
				String identity = rs.getString(7);
				String department = rs.getString(8);
				User u = new User();
				u.setId(id);
				u.setAccount(account);
				u.setName(name);
				u.setSex(sex);
				u.setAge(age);
				u.setIdentity(identity);
				u.setDepartment(department);
			
				list.add(u);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	
	}

	@Override
	public User queryOne(int id) {
		conn = DBUtil.getconn();
		User u = new User();
		try {
			ps = conn.prepareStatement("select *from user where id=?");
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()) {
				String account = rs.getString("account");
				String password = rs.getString("password");
				String name = rs.getString("name");
				String sex = rs.getString("sex");
				int age = rs.getInt("age");
				String department = rs.getString("department");
				u.setId(id);
				u.setAccount(account);
				u.setPassword(password);
				u.setName(name);
				u.setSex(sex);
				u.setAge(age);
				u.setDepartment(department);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				DBUtil.closeAll(conn, ps, rs);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return u;
	}
}
